package cn.com.dao;

import cn.com.entity.ClassTimetable;
import cn.com.entity.Scores;
import cn.com.entity.User;
import cn.com.tool.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Project:Academic - StudentDao
 *
 * @author 黄俊玮 On Administrator
 * @version 1.0
 * @DataTime 2024/6/14 15:28:42
 * @since 1.8
 */
public class StudentDao {
    public boolean modifyNow(String name,String pwd,int id){

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            connection = Jdbc.getConnection();
            preparedStatement= connection.prepareStatement("UPDATE t_user SET `name` = '"+name+"',pwd= "+pwd+" WHERE id = "+id);
             i = preparedStatement.executeUpdate();

        } catch (Exception e) {
            System.out.println("有异常抛出");
            e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }

        if ( i > 0){
            return true;
        }
        return false;
    }

    public List<ClassTimetable> find(String clazz){
        List<ClassTimetable> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null ;

        String sql = "SELECT `t_user`.id AS id,`t_user`.name AS `tname`,`t_task`.cid,`t_class`.name AS cname,`t_task`.coid,`t_courses`.c_name  \n" +
                "\tFROM t_task \n" +
                "\t\tRIGHT JOIN `t_user` ON `t_task`.tid = `t_user`.id \n" +
                "\t\tLEFT JOIN `t_class` ON `t_class`.id = `t_task`.cid \n" +
                "\t\tLEFT JOIN `t_courses` ON `t_task`.coid = `t_courses`.c_id \n" +
                "\t\tWHERE `t_user`.role =2 AND `t_task`.cid='"+clazz+"'";

        try{
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("tname");
                String cid = resultSet.getString("cid");
                String cname = resultSet.getString("cname");
                int c_id = resultSet.getInt("coid");
                String c_name = resultSet.getString("c_name");
                list.add(new ClassTimetable(id,name,cid,cname,c_id,c_name));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return list;
    }

    public List<Scores> findScores(int id){

        List<Scores> list = new ArrayList<>();

        String sql ="SELECT `t_class`.id AS cid,`t_class`.name AS cname,`t_user`.id AS sid,`t_user`.name AS sname,\n" +
                "c_id,c_name,scores,uptime FROM `t_score`\n" +
                "\tLEFT JOIN `t_user` ON `t_user`.id = `t_score`.sid\n" +
                "\tLEFT JOIN `t_class` ON `t_user`.clazz = `t_class`.id\n" +
                "\tLEFT JOIN `t_courses` ON `t_courses`.c_id = `t_score`.coid\n" +
                "\tWHERE sid = " + id;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                String cid = resultSet.getString("cid");
                String cname = resultSet.getString("cname");
                int sid = resultSet.getInt("sid");
                String sname = resultSet.getString("sname");
                int c_id = resultSet.getInt("c_id");
                String c_name = resultSet.getString("c_name");
                double scores = resultSet.getDouble("scores");
                String uptime = resultSet.getString("uptime");
                list.add(new Scores(cid,cname,sid,sname,c_id,c_name,scores,uptime));
                System.out.println("查询存在");
            }
        } catch (SQLException e) {
           e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return list;
    }
}
